﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CsharpCodeGenerator
{
    public class SqlScriptHelper
    {
        #region SpDescription
        public static string SpDescription
        {
            get
            {
                return @"-- ==========================================================================================
-- Entity Name: {0}
-- Author:  {1}
-- Create date: {2}
-- Description: {3}
-- ==========================================================================================

";
            }
        }
        #endregion

        #region DropPorcedure
        public static string DropPorcedure
        {
            get
            {
                return @"
--
-- Dropping stored procedure {0} : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[{0}]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[{0}]
  
GO
";
            }
        }
        #endregion

        #region SelectAll
        public static string SelectAll
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
@strSearch nvarchar(200)= N'',
@top int = 1,
@bottom int = 20
AS
BEGIN

SELECT * FROM (

SELECT *,row_number() over (order by ID) as r FROM {1} WHERE (ISDELETED = 0 OR ISDELETED is null)

)t WHERE r >=@top AND r <= @bottom

END

GO";
            }
        }
        #endregion

        #region SelectRow
        public static string SelectRow
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
{1}
AS
BEGIN

SELECT * FROM {2} WHERE ({3})  AND (ISDELETED = 0 OR ISDELETED is null)

END

GO";
            }
        }
        #endregion

        #region SelectRow
        public static string SelectRowSP
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
{1},
@strSearch nvarchar(200)= N'',
@top int = 1,
@bottom int = 20
AS
BEGIN

SELECT * FROM (

SELECT *, row_number() over (order by ID) as r FROM {2} WHERE ({3})  AND (ISDELETED = 0 OR ISDELETED is null)

)t WHERE r >=@top AND r <= @bottom

END

GO";
            }
        }
        #endregion

        #region InsertRow
        public static string InsertRow
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
{1}
AS
BEGIN

    INSERT INTO {2}
        ({3})
    VALUES
        ({4})
END

GO";
            }
        }
        #endregion

        #region UpdateRow
        public static string UpdateRow
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
{1}
AS
BEGIN

UPDATE {2} SET {3} WHERE {4}

END

GO";
            }
        }
        #endregion

        #region DeleteRow
        public static string DeleteRow
        {
            get
            {
                return @"
Create Procedure [dbo].[{0}]
{1}
AS
BEGIN

UPDATE {2} SET ISDELETED = 1,DELETEDDATE = GETDATE() WHERE {3}

END

GO";
            }
        }
        #endregion
    }
}
